

using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
namespace GeneralClasses
{

    ///holds title and keeps track of it in the database

    #region NOTES
    /* 29 NOV 2010 fixing known errors
     * 3 NOV 2010 cleaning up the class. 
13 OCT 2010 - Created class, cant put it in so nothing is tested, still writing in notepadd++.
*/
    #endregion


    public class Title
    {

        #region VARIABLES
        //==============================================================================
        //                                                                     VARIABLES
        //==============================================================================

        String name;
        int titleID;
        String acronym;

        DatabaseConnection dbc;

        #endregion

        #region PROPERTIES
        //==============================================================================
        //                                                                    PROPERTIES
        //==============================================================================

        public String Acronym
        {
            get { return acronym; }
            set { acronym = value; }
        }

        /*  public String ConnectionString
          {
              get { return dbc.ConnectionString; }
              set { dbc.ConnectionString = value; }
          }*/

        public int TitleID
        {
            get { return titleID; }
            set { titleID = value; }
        }

        public String Name
        {
            get { return name; }
            set { name = value; }
        }

        #endregion

        #region CONSTRUCTOR
        //==============================================================================
        //                                                                   CONSTRUCTOR
        //==============================================================================

        public Title()
        {
            initialize();
        }

        public Title(String name)
        {
            initialize();
            this.name = name;
            this.acronym = name.Substring(0, 3);
        }

        public void initialize()
        {
            titleID = -1;
            name = "employee";
            acronym = "em";

            dbc = new DatabaseConnection();
        }

        #endregion

        #region PRIVATE

        //==============================================================================
        //                                                                       PRIVATE
        //==============================================================================

        private int removeTitleRelations()
        {
            try
            {
                if (titleID != -1)
                {
                    dbc.query("DELETE FROM personhastitlerelationship WHERE tid='" + titleID + "'");
                    dbc.query("DELETE FROM departmentneedtitlerelationship WHERE tid='" + titleID + "'");
                    return 0;
                }

                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private bool checkTitleUsed()
        {
            try
            {
                DataTable table = dbc.query("SELECT * FROM personhastitlerelationship WHERE tid='" + titleID + "'");
                if ((table != null) && table.Rows.Count > 0) return true;

                table = dbc.query("SELECT * FROM departmentneedtitlerelationship WHERE tid='" + titleID + "'");
                if ((table != null) && table.Rows.Count > 0) return true;

                return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private bool checkTitleExists()
        {
            try
            {
                DataTable table = dbc.query("SELECT tid FROM Title WHERE name = '" + name + "'");
                return ((table != null) && table.Rows.Count > 0) ? (true) : (false);
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private int findTitleID()
        {
            try
            {
                DataTable table = dbc.query("SELECT tid FROM Title WHERE name = '" + name + "' AND acronym = '" + acronym + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        return (int)row["tid"];
                    }
                    return -1;
                }
                else return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        #endregion

        #region PUBLIC
        //==============================================================================
        //                                                                        PUBLIC
        //==============================================================================

        /// <summary>
        /// returns all titles in the database
        /// </summary>
        /// <returns></returns>
        public static List<Title> getAllTitles()
        {
            try
            {
                DatabaseConnection dbc = new DatabaseConnection();
                DataTable table = dbc.query("SELECT tid FROM Title");
                List<Title> titleList = new List<Title>();
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Title title = new Title();
                        title.titleID = (int)row["tid"];
                        title.loadData();
                        titleList.Add(title);
                    }
                }

                return titleList;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public static Title loadData(int tid)
        {
            Title title = new Title();
            title.TitleID = tid;
            title.loadData();
            return title;
        }

        public void loadData()
        {
            try
            {
                DataTable table = dbc.query("SELECT * FROM Title WHERE tid = '" + titleID + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        this.name = (string)row["name"];
                        this.acronym = (string)row["acronym"];
                    }

                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public int saveDataSimple()
        {
            try
            {
                if (titleID == -1)
                {
                    if (!checkTitleExists())
                    {
                        //add
                        dbc.executeNonQuery("INSERT INTO Title (name, acronym) values ('" + name + "','" + acronym + "')");
                    }
                    titleID = findTitleID();
                }
                else
                {
                    //save
                    dbc.executeNonQuery("UPDATE Title SET name='" + name + "', acronym='" + acronym + "' WHERE tid='" + titleID + "'");
                }

                return titleID;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public int saveData()
        {
            try
            {
                //update acronym
                acronym = name.Substring(0, 3);

                //exit if strings are unsafe
                if (!(UTIL.isStringSafeFromSQLInjection(name) && UTIL.isStringSafeFromSQLInjection(acronym))) { return -2; }

                //truncate giant strings
                name = name.Substring(0, Math.Min(32, name.Length));
                acronym = acronym.Substring(0, Math.Min(8, acronym.Length));

                if (titleID == -1)
                {
                    if (!checkTitleExists())
                    {
                        //add
                        dbc.executeNonQuery("INSERT INTO Title (name, acronym) values ('" + name + "','" + acronym + "')");
                    }
                    titleID = findTitleID();
                }
                else
                {
                    //save the title, but only if it's assumed new name doesnt already belong to another title
                    DataTable table = dbc.query("SELECT tid, name FROM Title WHERE name='" + this.name + "'"); // get all titles with this name
                    if (table != null)
                    {
                        //if there is one result ( test if its THIS title)
                        if (table.Rows.Count == 1)
                        {
                            foreach (DataRow row in table.Rows)
                            {
                                if ((int)row["tid"] == titleID)
                                { // now you can save
                                    dbc.executeNonQuery("UPDATE Title SET name='" + name + "', acronym='" + acronym + "' WHERE tid='" + titleID + "'");
                                }
                                break;
                            }
                        }
                        //if there are no results (name is unique to just save it
                        else if (table.Rows.Count == 0)
                        {
                            dbc.executeNonQuery("UPDATE Title SET name='" + name + "', acronym='" + acronym + "' WHERE tid='" + titleID + "'");
                            return titleID;
                        }
                        else return -2; // somehow there are 2 or more same names, so just quit
                    }
                    return -1;
                }
                return titleID;
            }
            catch (SqlException sqlE)
            {
                UTIL.sayDebug(sqlE);
                throw sqlE;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        public int removeData()
        {


            try
            {
                if (this.titleID != -1)
                {
                    removeTitleRelations();
                    if (!checkTitleUsed())
                    {
                        dbc.executeNonQuery("DELETE FROM Title WHERE tid='" + titleID + "'");
                        return 0;
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public void deprecated_deleteTitle() { }
        public void deprecated_addTitle() { }
        public int deprecated_getTitleID() { return -1; }

        #endregion

    } // end class


} //end namespace